package com.example.aling.happyenglish;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

// 数据库连接及数据操作类
public class MyHelper extends SQLiteOpenHelper {
    public MyHelper(Context context){
        super(context,"HappyWord.db",null,1);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建数据表
        db.execSQL("CREATE TABLE HappyWord(id INTEGER PRIMARY KEY AUTOINCREMENT, word VARCHAR(20),  familiar VARCHAR(10))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    // 添加数据
    public void add(ContentValues values,MyHelper myHelper){
        SQLiteDatabase db = myHelper.getWritableDatabase();  //获取可读写SQLiteDatabse对象
        List<DataType> temp = queryWords(myHelper,"word=?",values.getAsString("word"));
        // 查询word，若未添加过，则添加
        if (temp != null) return ;
        db.insert("HappyWord",null,values);
        db.close();
    }

    // 查询
    public List<DataType> queryWords(MyHelper myHelper, String selection, String param){
        Log.i("haha","query words");
        SQLiteDatabase db = myHelper.getReadableDatabase();
        Cursor cursor;
        List<DataType> result = new ArrayList<>(); // 存储查询结果
        String word,familiar;

        // 有查询参数，根据参数查询
        if (param != null)
            cursor = db.query("HappyWord",null,selection,new String[]{param},null,null,"word");
        else // 无查询参数，则查询全部
            cursor = db.query("HappyWord",null,null,null,null,null,"word");

        // 查询不到数据
        if (cursor.getCount() == 0) {
            return null;
        }
        // 获取第一条数据
        else {
            cursor.moveToFirst();
            word = cursor.getString(1);
            familiar = cursor.getString(2);
            result.add(new DataType(word,familiar));
        }
        // 继续获取数据
        while (cursor.moveToNext()) {
            word = cursor.getString(1);
            familiar = cursor.getString(2);
            result.add(new DataType(word,familiar));
        }
        cursor.close();
        db.close();
        return result;
        // 根据单词排序
//        cursor = db.query("HappyWord",null,null,null,null,null,"word");
//        // 根据熟悉程度排序
//        cursor = db.query("HappyWord",null,null,null,null,null,"familiar");
    }

    // 更新数据（根据word，更新对应的familiar熟悉程度）
    public void updateWord(ContentValues values,MyHelper myHelper){
        Log.i("haha","update word");
        SQLiteDatabase db = myHelper.getWritableDatabase();
        String word = values.get("word").toString();
        db.update("HappyWord",values,"word=?",new String[]{word});
        db.close();
    }


    // 删除数据
    public void deleteWord(MyHelper myHelper,String word){
        Log.i("haha","delete word");
        SQLiteDatabase db = myHelper.getWritableDatabase();
        db.delete("HappyWord","word=?",new String[]{word});
        db.close();
    }
}